#python database gui management
import pymysql
import os
import sys
from PyQt5.QtWidgets import (
    QWidget,
    QLabel,
    QLineEdit,
    QApplication,
    QPushButton,
    QTableWidget,
    QTableWidgetItem,
)
class mainwin(QWidget):
    # 主界面
    def __init__(self):
        super().__init__()
        self.init()
        self.db = pymysql.connect(host='127.0.0.1', user='root', passwd='123456', db='lrj1', port=3306)
        self.cursor = self.db.cursor()

    def init(self):
        self.setWindowTitle("数据库管理系统")
        self.setFixedSize(1040, 800)
        self.inputline = QTableWidget(self)
        self.inputline.setColumnCount(10)
        self.inputline.setRowCount(1)
        self.inputline.setHorizontalHeaderLabels(['日期','姓名','性别','年龄','电话','住址','症状及诊断','治疗方案','收取费用','欠费'])

        self.Tname = QLineEdit(self)
        self.Tdate = QLineEdit(self)

        self.label1 = QPushButton("抓取", self)
        self.label2 = QPushButton("更新", self)
        self.label3 = QPushButton("插入", self)
        self.label4 = QPushButton('删除', self)
        self.label5 = QPushButton('姓名查询', self)
        self.label6 = QPushButton('日期查询',self)
        self.label7 = QPushButton('尚未缴费查询',self)




        self.inputline.setGeometry(10, 20, 1020, 60)
        self.Tname.setGeometry(10,130,100,40)
        self.Tdate.setGeometry(210, 130, 100, 40)

        self.label1.setGeometry(10, 200, 60, 40)
        self.label2.setGeometry(80, 200, 60, 40)
        self.label3.setGeometry(150, 200, 60, 40)
        self.label4.setGeometry(220, 200, 60, 40)
        self.label5.setGeometry(110, 130, 80, 40)
        self.label6.setGeometry(310, 130, 80, 40)
        self.label7.setGeometry(400, 130, 120, 40)


        # 表格
        self.table = QTableWidget(self)
        self.table.setColumnCount(10)
        self.table.setHorizontalHeaderLabels(['日期','姓名','性别','年龄','电话','住址','症状及诊断','治疗方案','收取费用','欠费'])
        self.table.setGeometry(10, 260, 1020, 580)

        #监听
        self.label1.clicked.connect(self.showdata)
        self.label2.clicked.connect(self.update)
        self.label3.clicked.connect(self.add)
        self.label4.clicked.connect(self.delete)
        self.label5.clicked.connect(self.select)

        self.show()

    def showdata(self):
        sql = '''select * from main order by "sdate" asc'''
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        self.table.setRowCount(len(result))
        for oneline,n in zip(result,range(len(result))):
            for data ,m in zip(oneline,range(10)):
                self.table.setItem(n, m, QTableWidgetItem(data))



    def update(self):
        sql = ''''''

    def add(self):
        sql = '''insert into main(sdate,sname,sex,sage,sphone,shouse,swhat,solution,sin,son) values \
        (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'''
        row1 = str(self.inputline.item(0,0).text())
        row2 = str(self.inputline.item(0, 1).text())
        row3 = str(self.inputline.item(0, 2).text())
        row4 = str(self.inputline.item(0,3).text())
        row5 = str(self.inputline.item(0, 4).text())
        row6 = str(self.inputline.item(0, 5).text())
        row7 = str(self.inputline.item(0, 6).text())
        row8 = str(self.inputline.item(0, 7).text())
        row9 = str(self.inputline.item(0, 8).text())
        row10 = str(self.inputline.item(0, 9).text())



        try:
            self.cursor.execute(sql,(row1,row2,row3,row4,row5,row6,row7,row8,row9,row10))
            self.db.commit()
        except:
            print('插入信息缺失，补全后重试')

    def delete(self):
        sql = ''''''


    def select(self):
        name = self.Tname.text()
        sql = '''select * from main where sname=\'{}\''''.format(name)
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            self.table.setRowCount(len(result))
            for oneline, n in zip(result, range(len(result))):
                for data, m in zip(oneline, range(10)):
                    self.table.setItem(n, m, QTableWidgetItem(data))
        except:
            print('查询关键字错误')



if __name__ == "__main__":
    app = QApplication(sys.argv)
    mainwin = mainwin()
    sys.exit(app.exec_())



